/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import model.dto.Class;
import model.dto.User;
import java.sql.*;
import java.util.ArrayList;
import org.apache.log4j.Logger;
import model.util.DBUtil;

/**
 *
 * @author NHAN
 */
public class ClassDAO {
int totalRecords=0;   
   
    Connection conn;
    String sqlReadByID = "select * from Class where id=?";
    String sqlReadByStudent = "select * from class where class.id in (select distinct class_id from class_student  where student_id =?)";
    String sqlCreate = "insert into Class([name],created_date, is_public, created_by) values(?, ?, ?, ?)";
    String sqlUpdate = "update [Class] set [name]=?, is_public=? where id=?";
    String sqlDelete = "delete from Class where id=?";
    Logger logger = Logger.getLogger(ClassDAO.class);

    public ClassDAO() {
    }

    /**
     * get a Class by specifying identifier value
     *
     * @param id
     * @return Class object
     */
    public int getTotalRecords() {
        return totalRecords;
    }

    public Class getClass(int id) {
        Class c = new Class();
        try {
            conn = DBUtil.getConnection();

            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }//name,created_date, is_public
            PreparedStatement ps = conn.prepareStatement(sqlReadByID);
            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                c.setId(id);
                c.setName(rs.getString("name"));
                c.setCreatedDate(rs.getDate("created_date"));
                c.setIsPublic(rs.getBoolean("is_public"));
                c.getTeacher().setId(rs.getInt("created_by"));
            }
            DBUtil.disconnect();

        } catch (SQLException ex) {
            logger.info("SQL error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Error: " + ex.getMessage());
        }
        return c;
    }

    /**
     *
     * @param currentPage current page to select
     * @param pageSize number of records per page
     * @return list of Classes
     */
    public ArrayList<Class> getClasses(int currentPage, int pageSize) {
        ArrayList<Class> list = new ArrayList<Class>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Class", "id", "id");
            qo.setColumns("id, name,created_date, is_public,created_by");
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            //name,created_date, is_public
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Class c = new Class();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setCreatedDate(rs.getDate("created_date"));
                c.setIsPublic(rs.getBoolean("is_public"));
                c.getTeacher().setId(rs.getInt("created_by"));
                list.add(c);
            }
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    /**
     * get list of class according to user role
     * @param currentPage
     * @param pageSize
     * @return 
     */
    public ArrayList<Class> getClassesOfUser(int currentPage, int pageSize, int userId) {
        ArrayList<Class> list = new ArrayList<Class>();
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return null;
            }
            QueryObject qo = new QueryObject("Class", "id", "id");
            qo.setColumns("id, name,created_date, is_public, created_by");
            qo.setWhereClause("created_by=" + String.valueOf(userId));
            qo.setCurrentPage(currentPage);
            qo.setPageSize(pageSize);
            //name,created_date, is_public
            CallableStatement cs = qo.getCallableStatement(conn);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                Class c = new Class();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setCreatedDate(rs.getDate("created_date"));
                c.setIsPublic(rs.getBoolean("is_public"));
                c.getTeacher().setId(rs.getInt("created_by"));
                list.add(c);               
            }
             totalRecords=qo.getTotals();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return list;
    }

    public ArrayList<Class> getClassesOfStudent(User student) {
        ArrayList<model.dto.Class> list = new ArrayList<Class>();
        conn = DBUtil.getConnection();
        if (conn == null) {
            return null;
        }
        try {
            PreparedStatement ps = conn.prepareStatement(sqlReadByStudent);
            ps.setInt(1, student.getId());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Class cls = new Class();
                cls.setId(rs.getInt("id"));
                cls.setName(rs.getString("name"));
                cls.setIsPublic(rs.getBoolean("is_public"));
                cls.getTeacher().setId(rs.getInt("created_by"));
                cls.setCreatedDate(rs.getDate("created_date"));
                list.add(cls);
            }
        } catch (SQLException ex) {
            logger.info("Cannot get class list of student. " + ex.getMessage());
        } catch (Exception ex) {
            logger.info("Cannot get class list of student. " + ex.getMessage());
        }
        return list;
    }

    /**
     * add new Class
     *
     * @param c Class
     * @return greater than 0 value if the action is success, else return 0
     */
    public int create(Class c) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }//name,created_date, is_public
            PreparedStatement ps = conn.prepareStatement(sqlCreate);
            ps.setString(1, c.getName());
            ps.setDate(2, new java.sql.Date(c.getCreatedDate().getTime()));
            ps.setBoolean(3, c.getIsPublic());
            ps.setInt(4, c.getTeacher().getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info("SQL Error: " + ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * update specified Class
     *
     * @param c Class to update
     * @return 1 or greater if action is successful, else return 0
     */
    public int update(Class c) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlUpdate);
            ps.setString(1, c.getName());
            ps.setBoolean(2, c.getIsPublic());
            ps.setInt(3, c.getId());
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
            logger.info(ex.getMessage());
        } catch (Exception ex) {
            logger.info(ex.getMessage());
        }
        return effectedRows;
    }

    /**
     * delete a Class by specifying identifier value
     *
     * @param id
     * @return 1 or greater value if action is successful , else return 0
     */
    public int delete(int id) {
        int effectedRows = 0;
        try {
            conn = DBUtil.getConnection();
            if (conn == null) {
                logger.info("Cannot get connection");
                return 0;
            }
            PreparedStatement ps = conn.prepareStatement(sqlDelete);
            ps.setInt(1, id);
            effectedRows = ps.executeUpdate();
            DBUtil.disconnect();
        } catch (SQLException ex) {
        } catch (Exception ex) {
        }
        return effectedRows;
    }
}
